/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * @author luosisi
 * Date: 2010-7-5 上午11:02:15
 */
package com.ccxe.manage.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.ccxe.manage.dao.CustomerDAO;
import com.ccxe.manage.db.CcxDB;
import com.ccxe.manage.db.DBFactory;
import com.ccxe.manage.model.CustomerBean;
import com.ccxe.manage.service.transaction.TransactionCcxDb;
import com.ccxe.manage.util.StringValueValid;

/**
 * @author luosisi customer的增、删、改、查 操作
 */
public class CustomerDaoImpl extends CustomerDAO {

	public static String sqlKey = "select * from c_customer where status=0 ";

	/**
	 * 添加客户
	 */
	public int addCustormer(CustomerBean customer) {
		CcxDB ccxDB = null;
		int rs = 0;
		try {
			if (customer != null) {
				ccxDB = DBFactory.getInstance();
				String sqlKey = "insert into C_customer(CUSTOMER_ID,COMPANY,CONTACT_NAME,CONTACT_PHONE,"
						+ " CONTACT_FAX,CONTACT_EMAIL,STREET,CITY,COUNTRY,POST_CODE) "
						+ " values(?,?,?,?,?,?,?,?,?,?)";
				Object[] items = new Object[10];
				items[0] = customer.getCustomerID();
				items[1] = customer.getCompany();
				items[2] = customer.getContactName();
				items[3] = customer.getContactPhone();
				items[4] = customer.getContactFax();
				items[5] = customer.getContactEmail();
				items[6] = customer.getStreet();
				items[7] = customer.getCity();
				items[8] = customer.getCountry();
				items[9] = customer.getPostCode();

				rs = ccxDB.executeUpdate(sqlKey, items);
			}
		} catch (Exception e) {
			rs = 0;
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	/**
	 * 删除客户
	 * 
	 * @returned 受影响行数
	 */
	public int deleteCustomer(String cid) {
		TransactionCcxDb ccxDB = null;
		int rs = 0;
		try {
			if (cid != null) {
				ccxDB = new TransactionCcxDb();
				ccxDB.beginTransaction();
				String cus_del_sql = "update C_customer set STATUS='1' where CUSTOMER_ID=?";
				String user_delByCid_sql = "update C_user set USER_DELETED='1'  where CUSTOMER_ID=?";

				String[] items = { cid };
				int returnNumCus = ccxDB.executeUpdate(cus_del_sql, items);
				int returnNumUser = ccxDB.executeUpdate(user_delByCid_sql,
						items);
				ccxDB.commit();
				if (returnNumUser > 0 && returnNumCus > 0) {
					rs = 1;
				}
			}
		} catch (Exception e) {
			try {
				ccxDB.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			rs = 0;
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	/**
	 * 获得所有客户列表 进行cid / cname 的模糊查询
	 */
	public List<CustomerBean> getByCondition(String cid) {
		List<CustomerBean> list = new ArrayList<CustomerBean>();
		try {
			if (cid != null) {// 处理带条件的模糊查询
				sqlKey += " and CUSTOMER_ID like ? or COMPANY like ? ";
				String[] vals = { "%" + cid + "%", "%" + cid + "%" };
				list = this.getReadList(sqlKey, vals);
			} else {// 处理不带参数的查询,此处查询所有客户
				list = this.getReadList(sqlKey, null);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 根据id获得CustomerBean对象
	 */
	public CustomerBean getById(String cid) {
		CcxDB ccxDB = null;
		ResultSet rs = null;
		CustomerBean cb = null;
		try {
			if (cid != null && !cid.equals("")) {
				String sqlKey = "select * from C_CUSTOMER where CUSTOMER_ID=?";
				Object[] items = { cid };
				ccxDB = DBFactory.getInstance();
				rs = ccxDB.executeQuery(sqlKey, items);
				if (rs.next()) {
					cb = new CustomerBean();
					cb.setCustomerID(rs.getString("CUSTOMER_ID"));
					cb.setCompany(rs.getString("COMPANY"));
					cb.setContactName(rs.getString("CONTACT_NAME"));
					cb.setContactPhone(rs.getString("CONTACT_PHONE"));
					cb.setContactFax(rs.getString("CONTACT_FAX"));
					cb.setContactEmail(rs.getString("CONTACT_EMAIL"));
					cb.setStreet(rs.getString("STREET"));
					cb.setCity(rs.getString("CITY"));
					cb.setCountry(rs.getString("COUNTRY"));
					cb.setPostCode(rs.getString("POST_CODE"));
					cb.setLastUpdatetime(rs.getDate("LAST_UPDATETIME"));
					cb.setStatus(rs.getString("STATUS"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return cb;
	}
	

	/**
	 * 更新客户
	 */
	public int updateCustomer(CustomerBean customer) {
		CcxDB ccxDB = null;
		int rs = 0;
		try {
			if (customer != null) {
				ccxDB = DBFactory.getInstance();
				String sqlKey = "update c_customer set "
						+ " COMPANY=?,CONTACT_NAME=?,CONTACT_PHONE=?,"
						+ " CONTACT_FAX=?,CONTACT_EMAIL=?,STREET=?,CITY=?,"
						+ " COUNTRY=?,POST_CODE=?,LAST_UPDATETIME=?"
						+ " where CUSTOMER_ID=?";

				Object[] items = new Object[11];
				items[0] = customer.getCompany();
				items[1] = customer.getContactName();
				items[2] = customer.getContactPhone();
				items[3] = customer.getContactFax();
				items[4] = customer.getContactEmail();
				items[5] = customer.getStreet();
				items[6] = customer.getCity();
				items[7] = customer.getCountry();
				items[8] = customer.getPostCode();
				items[9] = new java.sql.Date(new java.util.Date().getTime());// datetime
				// Date
				items[10] = customer.getCustomerID();

				rs = ccxDB.executeUpdate(sqlKey, items);
			}
		} catch (Exception e) {
			rs = 0;
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}


	/**
	 * 私有--封装读取数据方法
	 * 
	 * @param sql
	 *            要执行的sql语句
	 * @param obj
	 *            参数数组,无参传null
	 * @return list
	 */
	private List<CustomerBean> getReadList(String sqlKey, Object[] items) {
		CcxDB ccxDB = null;
		ResultSet rs = null;
		List<CustomerBean> list = new ArrayList<CustomerBean>();
		try {
			ccxDB = DBFactory.getInstance();
			if (sqlKey != null && items != null) {
				rs = ccxDB.executeQuery(sqlKey, items);
			} else if (sqlKey != null) {
				rs = ccxDB.executeQuery(sqlKey);
			}
			while (rs.next()) {
				CustomerBean cb = new CustomerBean();
				cb.setCustomerID(rs.getString("CUSTOMER_ID"));
				cb.setCompany(rs.getString("COMPANY"));
				cb.setContactName(rs.getString("CONTACT_NAME"));
				cb.setContactPhone(rs.getString("CONTACT_PHONE"));
				cb.setContactFax(rs.getString("CONTACT_FAX"));
				cb.setContactEmail(rs.getString("CONTACT_EMAIL"));
				cb.setStreet(rs.getString("STREET"));
				cb.setCity(rs.getString("CITY"));
				cb.setCountry(rs.getString("COUNTRY"));
				cb.setPostCode(rs.getString("POST_CODE"));
				cb.setLastUpdatetime(rs.getDate("LAST_UPDATETIME"));
				cb.setStatus(rs.getString("STATUS"));
				list.add(cb);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	protected void buildSqlParameters(Map<String, String> map) {
		// 查询类别关键字: cus 关键字 | cusDate 日期区间 | combination 组合查询 | valiName验证注册ID
		pageSql = sqlKey;
		String sinfo = map.get("searchInfo");
		if (StringValueValid.isNotNullAndEmpty(sinfo)
				&& !"请输入公司名称或 ID".equals(sinfo)) {
			pageSql += " and ( CUSTOMER_ID like ? or COMPANY  like  ? )";
			pageParams.add("%" + map.get("searchInfo").trim() + "%");
			pageParams.add("%" + map.get("searchInfo").trim() + "%");
		}

		String begin = map.get("beginDate");
		if (StringValueValid.isNotNullAndEmpty(begin)) {
			pageSql += " and LAST_UPDATETIME >=  to_date(?,'YYYY-MM-DD') ";
			pageParams.add(begin.trim());
		}

		String end = map.get("endDate");
		if (StringValueValid.isNotNullAndEmpty(end)) {
			pageSql += " and LAST_UPDATETIME <=  to_date(?,'YYYY-MM-DD') ";
			pageParams.add(end.trim());
		}
	}
}
